CREATE     FUNCTION [dbo].[CalculateDeferralConversionAmount]
  (@invDate datetime, @amountDeferred decimal(18,4), @totalAmount decimal(18,4), @amountConverted decimal(18,4),
    @termsCount int, @termsEffectiveCount int, @termsType int, @termsDuration int, @effectiveDate datetime)
RETURNS decimal(18,4)
AS
BEGIN
DECLARE @amountToConvert decimal(18,4)
DECLARE @amountEligibleForConversion decimal(18,4)
DECLARE @amountPerInterval decimal(18,4)
DECLARE @intervalsPassed int
DECLARE @termsTypeYearly int
DECLARE @termsTypeQuarterly int
DECLARE @termsTypeMonthly int
DECLARE @termsTypeWeekly int
DECLARE @termsTypeDaily int
DECLARE @termsTypeEvent int
DECLARE @monthsPassed int
DECLARE @invDateDay int
DECLARE @effectiveDateDay int
DECLARE @dayAfterEffectiveDate int
DECLARE @decimalPlaces int

IF @amountConverted >= @totalAmount
    RETURN 0

IF @termsCount = 0
    RETURN 0

SET @termsTypeYearly = 0
SET @termsTypeQuarterly = 1
SET @termsTypeMonthly = 2
SET @termsTypeWeekly = 3
SET @termsTypeDaily = 4
SET @termsTypeEvent = 5

-- TODO: change the Currency table (or some similar mechanism) to carry the
-- number of decimal places to use when rounding.
SET @decimalPlaces = 2

SET @amountPerInterval = ROUND(@totalAmount / @termsCount, @decimalPlaces)

IF @termsType = @termsTypeYearly
    SET @intervalsPassed = DATEDIFF(Day, @invDate, @effectiveDate) / 365
ELSE IF @termsType = @termsTypeWeekly
    SET @intervalsPassed = DATEDIFF(Day, @invDate, @effectiveDate) / 7
ELSE IF @termsType = @termsTypeDaily
    SET @intervalsPassed = DATEDIFF(Day, @invDate, @effectiveDate)
ELSE IF @termsType = @termsTypeEvent
    SET @intervalsPassed = @termsEffectiveCount
ELSE
BEGIN    /* Either monthly or quarterly - deal with leap year issues... */
    SET @invDateDay = DATEPART(day, @invDate)
    SET @effectiveDateDay = DATEPART(day, @effectiveDate)
    SET @monthsPassed = DATEDIFF(Month, @invDate, @effectiveDate)
    IF @effectiveDateDay < @invDateDay
    BEGIN    /* If the invoice date day is on a day that equals the end of the month
        day of the effective date, let the months passed stay adjusted upward...
        (for example, if the invoice date is 1/30/04, and the effective date is
        2/29/04, we want this to show as a month passed...) */
        SET @dayAfterEffectiveDate = DATEPART(day, DATEADD(day, 1, @effectiveDate))
        IF @dayAfterEffectiveDate > @effectiveDateDay
            SET @monthsPassed = @monthsPassed - 1  /* (the effective date day is not the end of    
                                a month - otherwise day after would be the first
                                day of following month = 1, which < the previous day) */
    END
        
    IF @termsType = @termsTypeMonthly
        SET @intervalsPassed = @monthsPassed
    ELSE IF @termsType = @termsTypeQuarterly
        SET @intervalsPassed = @monthsPassed / 3
END

/* The first interval should actually take place ON @invDate...: */
IF @termsType != @termsTypeEvent
    SET @intervalsPassed = @intervalsPassed + 1

IF @intervalsPassed >= @termsCount
    RETURN @totalAmount - @amountConverted

SET @amountEligibleForConversion = @amountPerInterval * @intervalsPassed
IF @amountEligibleForConversion > @amountDeferred
    SET @amountEligibleForConversion = @amountDeferred
IF @amountConverted >= @amountEligibleForConversion
    SET @amountToConvert = 0
ELSE
    SET @amountToConvert = @amountEligibleForConversion - @amountConverted
RETURN @amountToConvert
END

GO